package dao;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.NotasTicket;

public class NotasTicketDao {

    public boolean insere(NotasTicket notasticket) throws SQLException, UsuarioDuplicadoException {
        String sql = "INSERT INTO tickets_notas(id_ticket,sequencial,descricao,status,id_usuario) VALUES(?,?,?,?,?)";
        bancoCasehd banco = new bancoCasehd();
        Connection con = (Connection) banco.getConexao().getInnerConnection();

        try {
            // pega o usuário logado na sessão
            PreparedStatement ptm = (PreparedStatement) con.prepareStatement(sql);
            ptm.setInt(1, notasticket.getId_ticket());
            ptm.setInt(2, getUltimoSequencial(notasticket.getId_ticket() ) );
            ptm.setString(3, notasticket.getDescricao());
            ptm.setInt(4, notasticket.getStatus());
            ptm.setInt(5, notasticket.getId_usuario());
            int row = ptm.executeUpdate();
            if (row == 1) {
                return true;
            }
        } catch (SQLException e) {
        }
        return false;
    }

    public ArrayList lista(Integer id) throws SQLException {
        NotasTicket notaTicket = null;
        ArrayList listTicket = new ArrayList();
        String sql = "select tkn.`id_ticket`, tkn.`sequencial`, tkn.`descricao`, "
                + "       tkn.`data_hora`, tkn.`status`, tkn.`id_usuario`, "
                + "       u.`nome` as nome_usuario "
                + "from tickets_notas tkn "
                + "join `usuarios` u on (u.`id` = tkn.`id_usuario`) "
                + "where tkn.`id_ticket` = ?";

        bancoCasehd banco = new bancoCasehd();
        java.sql.Connection con = banco.getConexao().getInnerConnection();

        PreparedStatement ptm = (PreparedStatement) con.prepareStatement(sql);
        ptm.setInt(1, id);
        ResultSet rs = null;
        rs = ptm.executeQuery();

        while (rs.next()) {
            notaTicket = new NotasTicket();
            notaTicket.setData_hora(rs.getTimestamp("data_hora").toString());
            notaTicket.setDescricao(rs.getString("descricao"));
            notaTicket.setId_ticket(rs.getInt("id_ticket"));
            notaTicket.setId_usuario(rs.getInt("id_usuario"));
            notaTicket.setNome_usuario(rs.getString("nome_usuario"));
            notaTicket.setSequencial(rs.getInt("sequencial"));
            notaTicket.setStatus(rs.getInt("status"));
            listTicket.add(notaTicket);
        }
        return listTicket;
    }

    private int getUltimoSequencial(int id_ticket) throws SQLException {
        String sql = "select max(sequencial) ultimo from tickets_notas "
                + "where id_ticket = ?";

        bancoCasehd banco = new bancoCasehd();
        java.sql.Connection con = banco.getConexao().getInnerConnection();

        PreparedStatement ptm = (PreparedStatement) con.prepareStatement(sql);
        ptm.setInt(1, id_ticket);
        ResultSet rs = null;
        rs = ptm.executeQuery();

        while (rs.next()) {
            return rs.getInt("ultimo") + 1;
        }
        return 1;
    }
}
